package com.webvihar.access2db.builders;

import java.util.List;

import com.webvihar.access2db.dbutils.AbstractDataTypes;
import com.webvihar.access2db.dbutils.MySqlDataTypes;
import com.webvihar.access2db.model.TableDataItem;
import com.webvihar.access2db.model.TableField;
import com.webvihar.access2db.model.TableRowItem;
import com.webvihar.common.utils.Logger;


public class MySQLQueryBuilder extends AbstractQueryBuilder{

	private String QUERY_SHOW_DATABASES = "SHOW DATABASES;";
	private String QUERY_SHOW_TABLES = "SHOW TABLES;";

	@Override
	public AbstractDataTypes getDataTypes() {
		return (new MySqlDataTypes());
	}

	@Override
	public String getShowTablesQuery() {
		return QUERY_SHOW_TABLES;
	} 

	@Override
	public String getAddDatabaseQuery(String dbName) {
		return String.format("CREATE DATABASE %s;",dbName);
	}

	@Override
	public String getShowDatabasesQuery() {
		return QUERY_SHOW_DATABASES;
	}

	@Override
	public String getEditDatabaseQuery(String oldDbName, String newDbName) {
		//RENAME DATABASE  old_dbname TO new_dbname;
		return String.format("RENAME DATABASE %s TO %s;", oldDbName, newDbName);
	}

	@Override
	public String getDeleteDatabaseQuery(String dbName) {
		//DROP DATABASE db_name;
		return String.format("DROP DATABASE %s;",dbName);
	}

	@Override
	public String getShowTableData(String tableName) {
		// SELECT * FROM table_name
		return String.format("SELECT * FROM %s;", tableName);
	}

	@Override
	public String getInsertTableDataQuery(String tableName, TableRowItem rowItem) {
		AbstractDataTypes dataTypes = getDataTypes();
		String parameters = "";
		String values = "";
		//INSERT INTO <table_name>(<param1>, <param2>, .....) VALUES (<value1>, <value1>, .....);
		String query = "";
		// add table parameters
		List<TableDataItem> items = rowItem.items; 
		for(int i=0; i<items.size(); i++) {
			TableDataItem item = items.get(i); 
			if(item.header != null) {
				parameters += (i == 0) ? String.format("( %s", item.header.title) : String.format(", %s", item.header.title);
				values += (i == 0) 
						? String.format("( %s", dataTypes.getProcessedParameter(item.data, item.header.type)) 
								: String.format(", %s", dataTypes.getProcessedParameter(item.data, item.header.type));
			}
		}
		parameters += (!parameters.equals("")) ? ")" : "";
		values += (!values.equals("")) ? ")" : "";
		query = String.format("INSERT INTO %s %s VALUES %s;",tableName, parameters, values);
		Logger.logInfo("InsertQuery", query);
		return query;
	}

	@Override
	public String getUpdateTableDataQuery(String tableName, TableRowItem oldRowItem, TableRowItem newRowItem) {
		AbstractDataTypes dataTypes = getDataTypes(); 
		//update details set name = 'oldValue' where id=3;
		String query = String.format("UPDATE %s SET ",tableName);
		for(int i=0; i<newRowItem.items.size(); i++) {
			TableDataItem newItem = newRowItem.items.get(i);
			if(newItem.data != null) {
				if(i == 0) {
					query += (newItem.data != null) ? String.format("%s = %s", newItem.header.title, dataTypes.getProcessedParameter(newItem.data, newItem.header.type)) : "";
				} else {
					query += (newItem.data != null) ? String.format(", %s = %s", newItem.header.title, dataTypes.getProcessedParameter(newItem.data, newItem.header.type)) : "";
				}
			}
		}
		query += " WHERE ";
		for(int i=0; i<oldRowItem.items.size(); i++) {
			TableDataItem oldItem = oldRowItem.items.get(i);
			if(oldItem.data != null && !oldItem.data.equals("")) {
				if(i == 0) {
					query += String.format("%s = %s", oldItem.header.title, dataTypes.getProcessedParameter(oldItem.data, oldItem.header.type));
				} else {
					query += String.format(" AND %s = %s", oldItem.header.title, dataTypes.getProcessedParameter(oldItem.data, oldItem.header.type));
				}
			}
		}
		query += ";";
		Logger.logInfo("UpdateQuery", query);
		return query;
	} 

	@Override
	public String getDeleteTableDataQuery(String tableName, TableRowItem oldRowItem) {
		AbstractDataTypes dataTypes = getDataTypes(); 
		//update details set name = 'oldValue' where id=3;
		String query = String.format("DELETE FROM %s WHERE ",tableName);
		for(int i=0; i<oldRowItem.items.size(); i++) {
			TableDataItem oldItem = oldRowItem.items.get(i);
			if(i == 0) {
				query += (oldItem.data != null) ? String.format("%s = %s", oldItem.header.title, dataTypes.getProcessedParameter(oldItem.data, oldItem.header.type)) : "";
			} else {
				query += (oldItem.data != null) ? String.format(" AND %s = %s", oldItem.header.title, dataTypes.getProcessedParameter(oldItem.data, oldItem.header.type)) : "";
			}
		}
		query += ";";
		Logger.logInfo("DeleteQuery", query);
		return query;
	}

	@Override
	public String getCreateTableQuery(String tableName, String comment, List<TableField> fields) {
		String query = String.format("CREATE TABLE `%s` ( ", tableName);
		// add fields to the query
		for(TableField field : fields) {
			query += String.format("`%s` %s(%s) ", field.fieldName, field.type, field.length);
			query += (field.unsigned) ? "UNSIGNED " : "";
			query += (field.zerofill) ? "ZEROFILL " : "";
			query += (field.allownull) ? "" : "NOT NULL ";
			//			query += (field.comment != null && field.comment.length() > 0) ? String.format("COMMENT `%s` ", field.comment) : "";
			query += (fields.indexOf(field) < (fields.size()-1)) ? ", " : "";
		}
		query += ") ";
		// add table properties
		//		query += (comment != null && comment.length() >0) ? String.format("COMMENT `%s` ", comment) : "";
		// TODO: collate default value
		query += "ENGINE=InnoDB ";
		query += "DEFAULT CHARSET=utf8 ";
		query += ";"; 
		return query;
	}

	@Override
	public String getEditTableQuery(String oldTableName, String tableName, List<TableField> oldFields, List<TableField> newFields) {
		// TODO : As of now, we are generating table rename query and drop query. Later we need to implement Alter table query
		
		// create statements
		String renameStr = (!oldTableName.equals(tableName)) ? String.format(" RENAME TO %s", tableName) : "";
		String dropStr = "";
		for(TableField oldField : oldFields) {
			boolean oldItemExist = false; 
			for(TableField newField : newFields) {
				if(oldField.isEquals(newField)) {
					oldItemExist = true;
					break;
				}
			}
			if(!oldItemExist) {
				dropStr += (dropStr.equals("")) ? String.format(" DROP %s", oldField.fieldName) : String.format(", DROP %s", oldField.fieldName); 
			}
		}
		
		// Create final query
		String query = String.format("ALTER TABLE %s", oldTableName);
		// Add Rename query
		query += renameStr;
		// Add drop query
		query += (dropStr.length() > 0) ? ((renameStr.length() > 0) ? String.format(", %s", dropStr) : String.format("	 %s", dropStr)) : "";
		query += ";";
		return query;
	}
	
	@Override
	public String getDeleteTableQuery(String tableName) {
		// DROP TABLE <table_name>;
		return String.format("DROP TABLE %s;", tableName);
	}
	
	@Override
	public String getTableDescriptionQuery(String tableName) {
		return String.format("DESC %s;", tableName);
	}
}